package database.pikeshitje;

import database.common.ConnectionFactory;
import model.magazina.FurnizimDetail;
import model.pikeshitje.Shitje;
import model.pikeshitje.ShitjeDetail;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   1/3/13
 * Time:   8:10 PM
 */
public class ShitjeDao {
    private static final String SHITJE_INSERT =
            "INSERT INTO pikeshitje.flete_shitje (id,operatori,status,data_krijimit,data_perfundimit,data_pageses,shuma_fatures,taksa,pikeshitje_id,shenime ) VALUES (?,?,?,?,?,?,?,?,?,?)";
    private static final String SHITJE_UPDATE =
            "UPDATE  pikeshitje.flete_shitje SET operatori=?,status=?,data_krijimit=?,data_perfundimit=?,data_pageses=?,shuma_fatures=?,taksa=?,pikeshitje_id=?,shenime=?  WHERE id  = ?";
    private static final String SELECT_SINGLE_SHITJE =
            "SELECT id,operatori,status,data_krijimit,data_perfundimit,data_pageses,shuma_fatures,taksa,pikeshitje_id,shenime FROM pikeshitje.flete_shitje WHERE id = ? ";
    private static final String SHITJE_DETAIL_INSERT =
            "INSERT INTO pikeshitje.shitje (fleta_id, produkt_id, sasia, cmimi) VALUES (?,?,?,?)";
    private static final String SELECT_SHITJE_DETAILS =
            "SELECT id, fleta_id, produkt_id, sasia, cmimi FROM pikeshitje.shitje WHERE fleta_id=?";
    private static final String SELECT_SHITJE_BY_DATE =
            "SELECT query_to_xml" +
            "('SELECT * from pikeshitje.flete_shitje WHERE magazina_id='||?||' AND date(data_perfundimit)>=date('||?||') AND date(data_perfundimit)<=date('||?||') ORDER BY data_perfundimit '," +
            "true,false,'')";

    private static int insert(Shitje shitje, Connection connection) throws SQLException {
        PreparedStatement statement = null;
        int rows = 0;
        if (connection == null || connection.isClosed()){
            return -1;
        }
        try {
            statement = connection.prepareStatement(SHITJE_INSERT);
            statement.setInt(1, shitje.getId());
            statement.setInt(2, shitje.getOperatorID());
            statement.setInt(3, shitje.getStatus());
            statement.setTimestamp(4, new Timestamp(shitje.getDataKrijimit().getTime()));
            if (shitje.getDataPerfundimit()!= null){
                statement.setTimestamp(5, new Timestamp(shitje.getDataPerfundimit().getTime()));
            } else {
                statement.setNull(5, Types.TIMESTAMP);
            }
            if (shitje.getDataPageses()!= null){
                statement.setTimestamp(6, new Timestamp(shitje.getDataPageses().getTime()));
            } else {
                statement.setNull(6, Types.TIMESTAMP);
            }
            statement.setDouble(7, shitje.getShumaFatures());
            statement.setDouble(8, shitje.getTaksa());
            statement.setInt(9, shitje.getPikeshitjeID());
            statement.setString(10, shitje.getShenime());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    private static int insertDetail(ShitjeDetail detail, Connection connection) throws SQLException {
        PreparedStatement statement = null;
        int rows = 0;
        if (connection == null || connection.isClosed()){
            return -1;
        }
        try {
            statement = connection.prepareStatement(SHITJE_DETAIL_INSERT);
            statement.setInt(1, detail.getFletaId());
            statement.setInt(2, detail.getProduktID());
            statement.setDouble(3, detail.getSasia());
            statement.setDouble(4, detail.getCmimi());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static boolean saveShitje(Shitje shitje){
        Connection connection = ConnectionFactory.getConnection();
        Savepoint startTransaction = null;
        try {
            connection.setAutoCommit(false);
            startTransaction = connection.setSavepoint();
            shitje.setId(getNextID());
            int row = insert(shitje, connection);
            if (row != 1){
                connection.rollback(startTransaction);
                return false;
            }
            List<ShitjeDetail> details=shitje.getDetailList();
            for (ShitjeDetail fdt:details){
                fdt.setFletaId(shitje.getId());
                row = insertDetail(fdt,connection);
                if (row!=1){
                    connection.rollback(startTransaction);
                    return false;
                }
            }
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback(startTransaction);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        } finally {
            try{
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public static int update(Shitje shitje){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SHITJE_UPDATE);
            statement.setInt(1, shitje.getOperatorID());
            statement.setInt(2, shitje.getStatus());
            statement.setTimestamp(3, new Timestamp(shitje.getDataKrijimit().getTime()));
            if (shitje.getDataPerfundimit()!= null){
                statement.setTimestamp(4, new Timestamp(shitje.getDataPerfundimit().getTime()));
            } else {
                statement.setNull(4, Types.TIMESTAMP);
            }
            if (shitje.getDataPageses()!= null){
                statement.setTimestamp(5, new Timestamp(shitje.getDataPageses().getTime()));
            } else {
                statement.setNull(5, Types.TIMESTAMP);
            }
            statement.setDouble(6, shitje.getShumaFatures());
            statement.setDouble(7, shitje.getTaksa());
            statement.setInt(8, shitje.getPikeshitjeID());
            statement.setString(9, shitje.getShenime());
            statement.setInt(10, shitje.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Shitje selectSingleShitje(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Shitje shitje = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_SHITJE);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                shitje = new Shitje();
                shitje.setId(resultSet.getInt(1));
                shitje.setOperatorID(resultSet.getInt(2));
                shitje.setStatus(resultSet.getInt(3));
                shitje.setDataKrijimit(resultSet.getTimestamp(4));
                shitje.setDataPerfundimit(resultSet.getTimestamp(5));
                shitje.setDataPageses(resultSet.getTimestamp(6));
                shitje.setShumaFatures(resultSet.getDouble(7));
                shitje.setTaksa(resultSet.getDouble(8));
                shitje.setPikeshitjeID(resultSet.getInt(9));
                shitje.setShenime(resultSet.getString(10));
                shitje.setDetailList(getShitjeDetails(shitje.getId()));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return shitje;
    }

    public static List<ShitjeDetail> getShitjeDetails(int shitjeID){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<ShitjeDetail> detailList = new ArrayList<ShitjeDetail>();
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SHITJE_DETAILS);
            statement.setInt(1, shitjeID);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                ShitjeDetail detail = new ShitjeDetail();
                detail.setId(resultSet.getInt(1));
                detail.setFletaId(resultSet.getInt(2));
                detail.setProduktID(resultSet.getInt(3));
                detail.setSasia(resultSet.getDouble(4));
                detail.setCmimi(resultSet.getDouble(5));
                detailList.add(detail);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return detailList;
    }

    public static String selectShitjeByUnitAndDate(int pikeshitje, java.util.Date startDate,java.util.Date endDate){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String shitjeList = "";
        try {
            connection = ConnectionFactory.getConnection();
            //TODO if magazina has childs
            statement = connection.prepareStatement(SELECT_SHITJE_BY_DATE);
            statement.setInt(1, pikeshitje);
            statement.setDate(2, new Date(startDate.getTime()));
            statement.setDate(3, new Date(endDate.getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                shitjeList = resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return shitjeList;
    }

    private static int getNextID() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int result =0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement("SELECT nextval('flete_shitje_id_seq')");
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
